package com.common.util.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseDao {
    /**
     * 查询sql，返回结果集List，每条记录为一个Map，key为字段名，value为字段值
     * 
     * @param sql
     * @return
     */
    public List<Map<String, Object>> executeSqlToList(String sql) {
        List<Map<String, Object>> list = null;
        DBUtil dbUtil = new DBUtil();
        Connection conn = dbUtil.getConnection();
        ResultSet rs = null;
        Statement statement = null;
        try {
            statement = conn.createStatement();
            System.out.println("开始执行sql");
            System.out.println(sql);
            rs = statement.executeQuery(sql);
            System.out.println("执行sql结束");
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            list = new ArrayList<Map<String, Object>>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
                }
                list.add(map);
            }
            rs.close();
        } catch (SQLException e) {
            System.out.println("执行sql异常：" + sql);
            e.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println("关闭statement异常：" + e.getMessage());
            }
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("关闭conn异常：" + e.getMessage());
            }
        }
        return list;
    }

    /**
     * 执行insert、update、delete语句，返回结果为更新数据数量
     * 
     * @param sql
     * @return
     */
    public int executeSql(String sql) {
        int result = 0;
        DBUtil dbUtil = new DBUtil();
        Connection conn = dbUtil.getConnection();
        Statement statement = null;
        try {
            System.out.println("开始执行sql");
            System.out.println(sql);
            statement = conn.createStatement();
            result = statement.executeUpdate(sql);
            System.out.println("执行sql结束");
        } catch (SQLException e) {
            System.out.println("执行sql异常：" + sql);
            e.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println("关闭statement异常：" + e.getMessage());
            }
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("关闭conn异常：" + e.getMessage());
            }
        }
        return result;
    }

    /**
     * 执行insert语句，返回结果为插入数据主键
     * 
     * @param sql
     * @return
     */
    public Object executeInsert(String sql) {
        Object id = null;
        DBUtil dbUtil = new DBUtil();
        Connection conn = dbUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getObject(1);
            }
        } catch (SQLException e) {
            System.out.println("执行sql异常：" + sql);
            e.printStackTrace();
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                System.out.println("关闭statement异常：" + e.getMessage());
            }
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("关闭conn异常：" + e.getMessage());
            }
        }
        return id;
    }

    /**
     * 执行只有1条记录的查询语句，返回map集合，key为字段名，value为字段值
     * 
     * @param sql
     * @return
     */
    public Map<String, Object> executeSqlToMap(String sql) {
        Map<String, Object> map = null;
        DBUtil dbUtil = new DBUtil();
        Connection conn = dbUtil.getConnection();
        ResultSet rs = null;
        Statement statement = null;
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                map = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
                }
                break;
            }
            rs.close();
        } catch (SQLException e) {
            System.out.println("执行sql异常：" + sql);
            e.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                System.out.println("关闭statement异常：" + e.getMessage());
            }
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("关闭conn异常：" + e.getMessage());
            }
        }
        return map;
    }

    /**
     * 添加信息
     * 
     * @param list
     * @param tableName
     */
    public int insert(List<Map<String, String>> list, String tableName) {
        Map<String, String> map = list.get(0);
        String column = "";
        String values = "";
        for (Map.Entry<String, String> entry : map.entrySet()) {
            if (entry.getValue() != null && !"".equals(entry.getValue()) && !"null".equals(entry.getValue())) {
                // 字段key和MySQL的保留字冲突了，当mysql的字段名和保留字冲突的时候，sql语句中的字段名需要加上反引号``来加以区别
                column += ",`" + entry.getKey() + "`";
                values += ",'" + entry.getValue() + "'";
            }
        }
        if ("".equals(column) || "".equals(values)) {
            System.out.println("检查参数");
            return -1;
        }
        column = column.substring(1);
        values = values.substring(1);
        String sql = "insert into " + tableName + " (" + column + ") values (" + values + ");";
        int result = this.executeSql(sql);
        return result;
    }
}
